﻿namespace FFF.Test.DataAccess.Framework.Configuration
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata;
    using FFF.Test.DataAccess.Framework.Configuration.Dto;

    /// <summary>
    /// 描述的配置
    /// </summary>
    public static class DescConfiguration
    {
        /// <summary>
        /// 存储列的信息
        /// </summary>
        private static List<ColInfoDto> _colInfoDtos = new List<ColInfoDto>();

        /// <summary>
        /// 是否已经执行
        /// </summary>
        private static bool _isAlreadyExcute = true;

        /// <summary>
        /// 初始化colInfo的信息
        /// </summary>
        /// <param name="entityType">builder</param>
        public static void InitColInfo(IMutableEntityType entityType)
        {
            string tableName = entityType.Relational().TableName;
            string tableDesc = entityType.ClrType.GetCustomAttributes(typeof(DisplayAttribute), false)
                .OfType<DisplayAttribute>().FirstOrDefault()?.Name;

            IEnumerable<IMutableProperty> props = entityType.GetProperties();
            foreach (IMutableProperty prop in props)
            {
                ColInfoDto infoDto = new ColInfoDto();
                infoDto.Type = "Table";
                if (tableName.ToLower().Contains("view"))
                {
                    infoDto.Type = "View";
                }

                infoDto.TableName = tableName;
                infoDto.TableDesc = tableDesc;

                System.Reflection.PropertyInfo porpType = prop.PropertyInfo;
                infoDto.ColDesc = porpType.GetCustomAttributes(typeof(DisplayAttribute), false)
                    .OfType<DisplayAttribute>().FirstOrDefault()?.Name;
                infoDto.ColumnName = prop.Relational().ColumnName;
                _colInfoDtos.Add(infoDto);
            }
        }

        /// <summary>
        /// 执行添加数据库的描述信息
        /// </summary>
        /// <param name="myDbContext">添加数据的描述信息</param>
        /// <returns>renwu</returns>
        public static async Task Excute(MyDbContext myDbContext)
        {
            if (!_isAlreadyExcute)
            {
                var groups = _colInfoDtos.GroupBy(x => new { x.TableName, x.TableDesc, x.Type }).Select(x => new { Keys = x.Key, Data = x });
                foreach (var group in groups)
                {
                    await SetTableDescription(myDbContext, group.Keys.TableName, group.Keys.TableDesc, group.Keys.Type);
                }

                foreach (ColInfoDto colInfo in _colInfoDtos)
                {
                    if (!string.IsNullOrEmpty(colInfo.ColDesc))
                    {
                        await SetColumnDescription(
                            myDbContext,
                            colInfo.TableName,
                            colInfo.ColumnName,
                            colInfo.ColDesc,
                            colInfo.Type);
                    }
                }

            }

            _isAlreadyExcute = true;
        }

        private static async Task SetColumnDescription(
            MyDbContext myDbContext,
            string tableName,
            string columnName,
            string description,
            string type)
        {
            string strGetDesc = $"select [value] from fn_listextendedproperty('MS_Description','schema','dbo','{type}',N'{tableName}','column',null) where objname = N'{columnName}';";
            string prevDesc = await myDbContext.ExecuteScalarAsync<string>(strGetDesc);
            string strExistsSql = $"SELECT OBJECT_ID('{tableName}')";
            int existis = await myDbContext.ExecuteScalarAsync<int>(strExistsSql);
            if (existis == 0)
            {
                return;
            }

            if (prevDesc == null)
            {
                await myDbContext.ExecuteAsync(
                    $@"EXEC sp_addextendedproperty 
        @name = N'MS_Description', @value = @desc,
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'{type}',  @level1name = @table,
        @level2type = N'Column', @level2name = @column;",
                                                       new SqlParameter("@table", tableName),
                                                       new SqlParameter("@column", columnName),
                                                       new SqlParameter("@desc", description));
            }
            else
            {
                await myDbContext.ExecuteAsync(
                    $@"EXEC sp_updateextendedproperty 
        @name = N'MS_Description', @value = @desc,
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'{type}',  @level1name = @table,
        @level2type = N'Column', @level2name = @column;",
                                                       new SqlParameter("@table", tableName),
                                                       new SqlParameter("@column", columnName),
                                                       new SqlParameter("@desc", description));
            }
        }

        private static async Task SetTableDescription(
            MyDbContext myDbContext,
            string tableName,
            string description,
            string type)
        {
            string strGetDesc = $"select [value] from fn_listextendedproperty('MS_Description','schema','dbo','{type}',N'{tableName}',null,null) where objname = N'{tableName}';";
            string prevDesc = await myDbContext.ExecuteScalarAsync<string>(strGetDesc);
            string strExistsSql = $"SELECT OBJECT_ID('{tableName}')";
            int existis = await myDbContext.ExecuteScalarAsync<int>(strExistsSql);
            if (existis == 0)
            {
                return;
            }

            if (prevDesc == null)
            {
                await myDbContext.ExecuteAsync(
                    $@"EXEC sp_addextendedproperty 
        @name = N'MS_Description', @value = @desc,
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'{type}',  @level1name = @table,
        @level2type = null, @level2name = null;",
                                                       new SqlParameter("@table", tableName),
                                                       new SqlParameter("@desc", description));
            }
            else
            {
                await myDbContext.ExecuteAsync(
                    $@"EXEC sp_updateextendedproperty 
        @name = N'MS_Description', @value = @desc,
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'{type}',  @level1name = @table,
        @level2type = null, @level2name = null",
                                                       new SqlParameter("@table", tableName),
                                                       new SqlParameter("@desc", description));
            }
        }
    }
}
